from tkinter import messagebox, ttk

import pymysql
import tkinter as tk

from matplotlib import pyplot as plt
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
host = 'localhost'
database = 'book'
name = 'root'
password = ''

conn = pymysql.connect(host=host,
                       port=3307,
                       user= name,
                       password=password,
                       db=database)

cursor = conn.cursor()


app = tk.Tk()



# 主菜单
def memu():
    app.geometry("600x400")
    app.title('图书管理系统')
    label = tk.Label(app, text='欢迎使用图书管理系统', font=('宋体', 25))
    label.place(x=130, y=40)
    button = tk.Button(app, text="管理员登录", font=("宋体", 15),command=admin)
    button.place(x=220, y=150)
    button = tk.Button(app, text="借阅员登录", font=("宋体", 15),command=reader)
    button.place(x=220, y=200)
    button = tk.Button(app, text="学生登录", font=("宋体", 15),command=student_memu)
    button.place(x=220, y=250)
    app.mainloop()


# 学生
def student_memu():
    #app.destroy()
    global student_memu
    student_memu = tk.Tk()
    student_memu.geometry("600x400")
    student_memu.title('学生登录')
    label = tk.Label(student_memu, text='账号:', font=('宋体', 15))
    label.place(x=130, y=40)
    label = tk.Label(student_memu, text='密码:', font=('宋体', 15))
    label.place(x=130, y=80)
    name_entry = tk.Entry(student_memu, font=('宋体', 15))
    name_entry.pack()
    name_entry.place(x=200, y=40)
    student_password = tk.Entry(student_memu, show='*', font=('宋体', 15))
    student_password.pack()
    student_password.place(x=200, y=80)
    button = tk.Button(student_memu, text="登录", font=("宋体", 15),
                       command=lambda: get_student_info(name_entry, student_password))
    button.place(x=150, y=200)
    button = tk.Button(student_memu, text="注册", font=("宋体", 15), command= student_register)
    button.place(x=350, y=200)

# 学生注册
def student_register():
    student_memu.destroy()
    student_register = tk.Tk()
    student_register.geometry("600x400")
    student_register.title('学生注册')
    label = tk.Label(student_register, text='学号:', font=('宋体', 15))
    label.place(x=130, y=40)
    label = tk.Label(student_register, text='姓名:', font=('宋体', 15))
    label.place(x=130, y=80)
    label = tk.Label(student_register, text='性别:', font=('宋体', 15))
    label.place(x=130, y=120)
    label = tk.Label(student_register, text='出生日期:', font=('宋体', 15))
    label.place(x=130, y=160)
    label = tk.Label(student_register, text='班级名:', font=('宋体', 15))
    label.place(x=130, y=200)
    label = tk.Label(student_register, text='密码:', font=('宋体', 15))
    label.place(x=130, y=240)
    uname_register = tk.Entry(student_register, font=('宋体', 15))
    uname_register.pack()
    uname_register.place(x=250, y=40)
    name_register = tk.Entry(student_register, font=('宋体', 15))
    name_register.pack()
    name_register.place(x=250, y=80)
    sex_register = tk.Entry(student_register, font=('宋体', 15))
    sex_register.pack()
    sex_register.place(x=250, y=120)
    date_register = tk.Entry(student_register, font=('宋体', 15))
    date_register.pack()
    date_register.place(x=250, y=160)
    class_register = tk.Entry(student_register, font=('宋体', 15))
    class_register.pack()
    class_register.place(x=250, y=200)
    password_register = tk.Entry(student_register, font=('宋体', 15))
    password_register.pack()
    password_register.place(x=250, y=240)
    button = tk.Button(student_register, text="注册", font=("宋体", 15),
                           command=lambda: student_register_info(uname_register,
                                                                name_register,
                                                                sex_register,
                                                                date_register,
                                                                class_register,
                                                                password_register,
                                                                ) )
    button.place(x=250, y=300)


# 学生注册实现

def student_register_info(uname_register,name_register,sex_register,date_register,class_register,password_register):
    try:
        uname = uname_register.get()
        password = password_register.get()
        name = name_register.get()
        sex = sex_register.get()
        date = date_register.get()
        class_ = class_register.get()
        sql = 'insert into student values (%s,%s,%s,%s,%s,%s)'
        cursor.execute(sql, (uname, name, sex,date,class_,password))
        conn.commit()
        messagebox.showinfo("注册提醒", "注册成功")
    except:
        messagebox.showinfo("注册提醒", "注册失败，账号已经存在")


# 学生登录
def get_student_info(name_entry,student_password):
    try:
        global student_name
        student_name = name_entry.get()
        password = student_password.get()
        sql = 'select * from student where sid = %s'
        cursor.execute(sql, student_name)
        data = cursor.fetchall()
        info_data = data[0]
        if student_name == info_data[0] and password == info_data[5]:
            messagebox.showinfo("登录提醒","登录成功")
            student_select()
        else:
            messagebox.showinfo("登录提醒","登录失败，用户名或密码错误")
    except:
        messagebox.showinfo("登录提醒","登录失败，用户名或密码错误")

# 学生操作
def student_select():
    #student_memu.destroy()
    global student_select
    student_select = tk.Tk()
    student_select.title('学生操作')
    student_select.geometry("600x400")
    button = tk.Button(student_select, text="查看馆藏图书信息", font=("宋体", 15), command=location_book)
    button.place(x=220, y=150)
    button = tk.Button(student_select, text="查看借阅图书信息", font=("宋体", 15), command=location_booking)
    button.place(x=220, y=200)


# 馆藏图书实现
def location_book():
    location_book = tk.Tk()
    location_book.title('馆藏图书')
    location_book.geometry("600x400")
    sql = 'select * from book'
    cursor.execute(sql)
    data1 = cursor.fetchall()
    data = []
    for item in data1:
        i_list = []
        for i in item:
            i_list.append(i)
        data.append(i_list)
    table = ttk.Treeview(location_book,
                         columns=("书号", "书名", "作者","出版社", "出版日期", "简介","价格","数量"),
                         show="headings")
    table.column("书号", width=60)
    table.column("书名", width=60)
    table.column("作者", width=60)
    table.column("出版社", width=60)
    table.column("出版日期", width=60)
    table.column("简介", width=60)
    table.column("价格", width=60)
    table.column("数量", width=60)
    table.heading("书号", text="书号")
    table.heading("书名", text="书名")
    table.heading("作者", text="作者")
    table.heading("出版社", text="出版社")
    table.heading("出版日期", text="出版日期")
    table.heading("简介", text="简介")
    table.heading("价格", text="价格")
    table.heading("数量", text="数量")
    # 插入表格数据
    for row in data:
        table.insert("", "end", values=row)
    # 将表格添加到主窗口
    table.pack()

# 借阅量

def location_booking():
    location_booking = tk.Tk()
    location_booking.title('借阅图书')
    location_booking.geometry("600x400")
    sql = 'select * from reading where sid = %s'
    cursor.execute(sql,student_name)
    data1 = cursor.fetchall()
    data = []
    for item in data1:
        i_list = []
        for i in item:
            i_list.append(i)
        data.append(i_list)
    table = ttk.Treeview(location_booking,
                         columns=("学号", "书号", "借阅日期", "还书日期"),
                         show="headings")
    table.column("学号", width=100)
    table.column("书号", width=100)
    table.column("借阅日期", width=100)
    table.column("还书日期", width=100)
    table.heading("学号", text="学号")
    table.heading("书号", text="书号")
    table.heading("借阅日期", text="借阅日期")
    table.heading("还书日期", text="还书日期")
    # 插入表格数据
    for row in data:
        table.insert("", "end", values=row)
    # 将表格添加到主窗口
    table.pack()





# 借阅员
def reader():
    #app.destroy()
    global reader
    reader = tk.Tk()
    reader.geometry("600x400")
    reader.title('借阅员登录')
    label = tk.Label(reader, text='账号:', font=('宋体', 15))
    label.place(x=130, y=40)
    label = tk.Label(reader, text='密码:', font=('宋体', 15))
    label.place(x=130, y=80)
    name_entry = tk.Entry(reader, font=('宋体', 15))
    name_entry.pack()
    name_entry.place(x=200, y=40)
    reader_password = tk.Entry(reader, show='*', font=('宋体', 15))
    reader_password.pack()
    reader_password.place(x=200, y=80)
    button = tk.Button(reader, text="登录", font=("宋体", 15),
                       command=lambda: get_reader_info(name_entry, reader_password))
    button.place(x=150, y=200)
    button = tk.Button(reader, text="注册", font=("宋体", 15),command=reader_register)
    button.place(x=350, y=200)


# 借阅员注册
def reader_register():
    #reader.destroy()
    reader_register = tk.Tk()
    reader_register.geometry("600x400")
    reader_register.title('借阅员注册')
    label = tk.Label(reader_register, text='账号:', font=('宋体', 15))
    label.place(x=130, y=40)
    label = tk.Label(reader_register, text='密码:', font=('宋体', 15))
    label.place(x=130, y=80)
    label = tk.Label(reader_register, text='姓名:', font=('宋体', 15))
    label.place(x=130, y=120)
    label = tk.Label(reader_register, text='性别:', font=('宋体', 15))
    label.place(x=130, y=160)
    uname_register = tk.Entry(reader_register, font=('宋体', 15))
    uname_register.pack()
    uname_register.place(x=200, y=40)
    password_register = tk.Entry(reader_register, font=('宋体', 15))
    password_register.pack()
    password_register.place(x=200, y=80)
    name_register = tk.Entry(reader_register, font=('宋体', 15))
    name_register.pack()
    name_register.place(x=200, y=120)
    sex_register = tk.Entry(reader_register, font=('宋体', 15))
    sex_register.pack()
    sex_register.place(x=200, y=160)
    button = tk.Button(reader_register, text="注册", font=("宋体", 15),
                       command=lambda: reader_register_info(uname_register,
                                                         password_register,
                                                         name_register,
                                                         sex_register
                                                         )
                       )
    button.place(x=200, y=250)

def reader_register_info(uname_register,password_register,name_register,sex_register):
    try:
        uname = uname_register.get()
        password = password_register.get()
        name = name_register.get()
        sex = sex_register.get()
        sql = 'insert into login_reader values (%s,%s,%s,%s)'
        cursor.execute(sql, (uname, password, name, sex))
        conn.commit()
        messagebox.showinfo("注册提醒","注册成功")
    except:
        messagebox.showinfo("注册提醒","注册失败，账号已经存在")




def get_reader_info(name_entry,reader_password):
    try:
        name = name_entry.get()
        password = reader_password.get()
        sql = 'select * from login_reader where account = %s'
        cursor.execute(sql, name)
        data = cursor.fetchall()
        info_data = data[0]
        if name == info_data[0] and password == info_data[1]:
            messagebox.showinfo("登录提醒","登录成功")
            read1()
        else:
            messagebox.showinfo("登录提醒","登录失败，用户名或密码错误")
    except:
        messagebox.showinfo("登录提醒","登录失败，用户名或密码错误")


def read1():
    #reader.destroy()
    global read1
    read1 = tk.Tk()
    read1.geometry("600x400")
    read1.title('借阅/归还系统')
    button = tk.Button(read1, text="借阅登记", font=("宋体", 15), command=borrowing)
    button.place(x=220, y=150)
    button = tk.Button(read1, text="归还登记", font=("宋体", 15),command=return_book)
    button.place(x=220, y=200)

# 借阅
def borrowing():
    #read1.destroy()
    borrowing = tk.Tk()
    borrowing.geometry("600x400")
    borrowing.title('图书借阅录入')
    label = tk.Label(borrowing, text='学号:', font=('宋体', 15))
    label.place(x=130, y=40)
    label = tk.Label(borrowing, text='书号:', font=('宋体', 15))
    label.place(x=130, y=80)
    label = tk.Label(borrowing, text='借阅日期:', font=('宋体', 15))
    label.place(x=130, y=120)
    label = tk.Label(borrowing, text='借阅员号:', font=('宋体', 15))
    label.place(x=130, y=160)
    in_sid = tk.Entry(borrowing, font=('宋体', 15))
    in_sid.pack()
    in_sid.place(x=240, y=40)
    in_bid = tk.Entry(borrowing, font=('宋体', 15))
    in_bid.pack()
    in_bid.place(x=240, y=80)
    in_date = tk.Entry(borrowing, font=('宋体', 15))
    in_date.pack()
    in_date.place(x=240, y=120)
    in_rid = tk.Entry(borrowing, font=('宋体', 15))
    in_rid.pack()
    in_rid.place(x=240, y=160)
    button = tk.Button(borrowing, text="录入", font=("宋体", 15),
                       command=lambda: input_borrowing(in_sid,
                                                     in_bid,
                                                     in_date,
                                                     in_rid,))
    button.place(x=480, y=280)

# 借阅信息写入
def input_borrowing(in_sid,in_bid,in_date,in_rid):
    try:
        sid = in_sid.get()
        bid = in_bid.get()
        date = in_date.get()
        rid = in_rid.get()
        # 根据书号，将图书减1
        sql = 'select number from book where bid = %s'
        cursor.execute(sql,bid)
        data = cursor.fetchall()
        temp = data[0]
        if temp[0] - 1 >= 0:
            sql = 'update book set number = number - 1'
            cursor.execute(sql)
            conn.commit()
            sql = 'insert into reading (sid,bid,readDate,readId) VALUES (%s,%s,%s,%s)'
            cursor.execute(sql, (sid, bid, date, rid))
            conn.commit()
            messagebox.showinfo("录入提醒", "录入成功")
        else:
            messagebox.showinfo("录入提醒", "该书已借阅完，无法借阅")
    except:
        messagebox.showinfo("录入提醒","录入失败")

# 归还
def return_book():
    #read1.destroy()
    borrowing = tk.Tk()
    borrowing.geometry("600x400")
    borrowing.title('图书归还录入')
    label = tk.Label(borrowing, text='学号:', font=('宋体', 15))
    label.place(x=130, y=40)
    label = tk.Label(borrowing, text='书号:', font=('宋体', 15))
    label.place(x=130, y=80)
    label = tk.Label(borrowing, text='归还日期:', font=('宋体', 15))
    label.place(x=130, y=120)
    in_sid = tk.Entry(borrowing, font=('宋体', 15))
    in_sid.pack()
    in_sid.place(x=240, y=40)
    in_bid = tk.Entry(borrowing, font=('宋体', 15))
    in_bid.pack()
    in_bid.place(x=240, y=80)
    in_date = tk.Entry(borrowing, font=('宋体', 15))
    in_date.pack()
    in_date.place(x=240, y=120)
    button = tk.Button(borrowing, text="录入", font=("宋体", 15),
                       command=lambda: input_return_borrowing(in_sid,
                                                       in_bid,
                                                       in_date,
                                                       ))
    button.place(x=480, y=280)


def input_return_borrowing(in_sid,in_bid,in_date):
    try:
        sid = in_sid.get()
        bid = in_bid.get()
        date = in_date.get()
        sql = 'update reading set returnDate = %s where sid = %s and bid = %s'
        cursor.execute(sql, (date, sid, bid))
        conn.commit()
        affected_rows = cursor.rowcount
        # 更新成功，根据书号，将其加一
        if affected_rows != 0:
            sql = 'update book set number = number + 1 where bid = %s'
            cursor.execute(sql, bid)
            conn.commit()
            messagebox.showinfo("归还提醒", "归还成功")
        else:
            messagebox.showinfo("归还提醒", "归还失败")
    except:
        print("错误")



# 管理员
def admin():
    #app.destroy()
    global admin_login
    admin_login = tk.Tk()
    admin_login.geometry("600x400")
    admin_login.title('管理员登录')
    label = tk.Label(admin_login, text='账号:', font=('宋体', 15))
    label.place(x=130, y=40)
    label = tk.Label(admin_login, text='密码:', font=('宋体', 15))
    label.place(x=130, y=80)
    name_entry = tk.Entry(admin_login,font=('宋体', 15))
    name_entry.pack()
    name_entry.place(x=200,y=40)
    admin_password = tk.Entry(admin_login,show='*',font=('宋体',15))
    admin_password.pack()
    admin_password.place(x=200, y=80)
    button = tk.Button(admin_login, text="登录", font=("宋体", 15),command=lambda:get_info(name_entry, admin_password))
    button.place(x=150, y=200)
    button = tk.Button(admin_login, text="注册", font=("宋体", 15),
                       command=admin_register)
    button.place(x=350, y=200)

# 获取管理员输入的细信息
def get_info(name_entry, admin_password):
    try:
        name_info = name_entry.get()
        pwd_info = admin_password.get()
        # 操作数据库
        sql = 'select * from login_admin where account = %s'
        cursor.execute(sql, name_info)
        data = cursor.fetchall()
        data_temp = data[0]
        if data_temp[0] == name_info and data_temp[1] == pwd_info:
            messagebox.showinfo('登录提示','登录成功')
            admin_memu()
        else:
            messagebox.showinfo('登录提示','登录失败，账号或密码不正确')
    except:
        messagebox.showinfo('登录提示','登录失败，账号或密码不正确')

# 管理员注册
def admin_register():
   # admin_login.destroy()
    admin_register = tk.Tk()
    admin_register.geometry("600x400")
    admin_register.title('管理员注册')
    label = tk.Label(admin_register, text='账号:', font=('宋体', 15))
    label.place(x=130, y=40)
    label = tk.Label(admin_register, text='密码:', font=('宋体', 15))
    label.place(x=130, y=80)
    label = tk.Label(admin_register, text='姓名:', font=('宋体', 15))
    label.place(x=130, y=120)
    label = tk.Label(admin_register, text='性别:', font=('宋体', 15))
    label.place(x=130, y=160)
    uname_register = tk.Entry(admin_register, font=('宋体', 15))
    uname_register.pack()
    uname_register.place(x=200, y=40)
    password_register = tk.Entry(admin_register,font=('宋体', 15))
    password_register.pack()
    password_register.place(x=200, y=80)
    name_register = tk.Entry(admin_register, font=('宋体', 15))
    name_register.pack()
    name_register.place(x=200, y=120)
    sex_register = tk.Entry(admin_register, font=('宋体', 15))
    sex_register.pack()
    sex_register.place(x=200, y=160)
    button = tk.Button(admin_register, text="注册", font=("宋体", 15),
                       command=lambda:get_register_info(uname_register,
                                               password_register,
                                               name_register,
                                               sex_register
                                               )
                       )
    button.place(x=200, y=250)
    # button = tk.Button(admin_register, text="取消", font=("宋体", 15), )
    # button.place(x=350, y=250)

# 管理员注册写入数据库
def get_register_info(uname_register,password_register,name_register,sex_register):
    try:
        uname = uname_register.get()
        password = password_register.get()
        name = name_register.get()
        sex = sex_register.get()
        sql = 'insert into login_admin values (%s,%s,%s,%s)'
        cursor.execute(sql,(uname,password,name,sex))
        conn.commit()
        messagebox.showinfo('注册提示','注册成功')
    except:
        messagebox.showinfo('注册提示', '注册失败，账号可能存在')


# 管理员界面
def admin_memu():
    #admin_login.destroy()
    global admin_memu
    admin_memu = tk.Tk()
    admin_memu.geometry("600x400")
    admin_memu.title('管理员系统')
    button = tk.Button(admin_memu, text="录入图书信息", font=("宋体", 15),command=input_book)
    button.place(x=220, y=150)
    button = tk.Button(admin_memu, text="查询图书信息", font=("宋体", 15),command=book_sort)
    button.place(x=220, y=200)

# 图书排序
def book_sort():
    #admin_memu.destroy()
    book_sort = tk.Tk()
    book_sort.geometry("600x400")
    book_sort.title('图书排序')
    fig = plt.figure()
    ax = fig.add_subplot(111)
    sql = 'select name from book'
    cursor.execute(sql)
    book_name = cursor.fetchall()
    book_name_list = []
    for item in book_name:
        for i in item:
            book_name_list.append(i)
    sql = 'select number from book'
    cursor.execute(sql)
    book_num = cursor.fetchall()
    book_num_list = []
    for item in book_num:
        for i in item:
            book_num_list.append(i)
    ax.bar(book_name_list, book_num_list)
    canvas = FigureCanvasTkAgg(fig, master=book_sort)
    canvas.draw()
    canvas.get_tk_widget().pack(side=tk.TOP, fill=tk.BOTH, expand=1)


# 录入图书
def input_book():
    #admin_memu.destroy()
    input_book = tk.Tk()
    input_book.geometry("600x400")
    input_book.title('图书录入')
    label = tk.Label(input_book, text='书号:', font=('宋体', 15))
    label.place(x=130, y=40)
    label = tk.Label(input_book, text='书名:', font=('宋体', 15))
    label.place(x=130, y=80)
    label = tk.Label(input_book, text='作者:', font=('宋体', 15))
    label.place(x=130, y=120)
    label = tk.Label(input_book, text='出版社:', font=('宋体', 15))
    label.place(x=130, y=160)
    label = tk.Label(input_book, text='出版日期:', font=('宋体', 15))
    label.place(x=130, y=200)
    label = tk.Label(input_book, text='内容简介:', font=('宋体', 15))
    label.place(x=130, y=240)
    label = tk.Label(input_book, text='价格:', font=('宋体', 15))
    label.place(x=130, y=280)
    label = tk.Label(input_book, text='数量:', font=('宋体', 15))
    label.place(x=130, y=320)
    in_bid = tk.Entry(input_book, font=('宋体', 15))
    in_bid.pack()
    in_bid.place(x=240, y=40)
    in_name = tk.Entry(input_book, font=('宋体', 15))
    in_name.pack()
    in_name.place(x=240, y=80)
    in_author = tk.Entry(input_book, font=('宋体', 15))
    in_author.pack()
    in_author.place(x=240, y=120)
    in_press = tk.Entry(input_book, font=('宋体', 15))
    in_press.pack()
    in_press.place(x=240, y=160)
    in_pt = tk.Entry(input_book, font=('宋体', 15))
    in_pt.pack()
    in_pt.place(x=240, y=200)
    in_itc = tk.Entry(input_book, font=('宋体', 15))
    in_itc.pack()
    in_itc.place(x=240, y=240)
    in_price = tk.Entry(input_book, font=('宋体', 15))
    in_price.pack()
    in_price.place(x=240, y=280)
    in_num = tk.Entry(input_book, font=('宋体', 15))
    in_num.pack()
    in_num.place(x=240, y=320)
    button = tk.Button(input_book, text="录入", font=("宋体", 15),
                       command=lambda: get_book_info(in_bid,
                                                         in_name,
                                                         in_author,
                                                         in_press,
                                                         in_pt,
                                                         in_itc,
                                                         in_price,
                                                         in_num,
                                                         )
                       )
    button.place(x=480, y=280)

# 图书写入数据库
def get_book_info(in_bid,in_name,in_author,in_press, in_pt,in_itc,in_price,in_num,):
    try:
        bid = in_bid.get()
        name = in_name.get()
        author = in_author.get()
        press = in_press.get()
        pt = in_pt.get()
        itc = in_itc.get()
        price = in_price.get()
        num = in_num.get()
        sql = 'insert into book values (%s,%s,%s,%s,%s,%s,%s,%s)'
        cursor.execute(sql,(bid,name,author,press,pt,itc,price,num))
        conn.commit()
        messagebox.showinfo('录入图书',"图书录入成功")
    except:
        messagebox.showinfo('录入图书',"图书录入失败，可能书号重复")



if __name__ == "__main__":
    memu()